﻿Imports BusinessRule.IBusiness
Imports System.Data
Imports System.Text
Imports DatabaseAccess.DBUtility
Imports BusinessRule.DTO

Public Class uLMSFocusProductMaintBusiness
    Implements uLMSFocusProductMaintIBusiness

    ''' <summary>
    ''' 获取关注产品
    ''' </summary>
    ''' <param name="paraDic"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetFocusProduct(ByVal paraDic As System.Collections.Generic.Dictionary(Of String, String)) As uMESProcessResult Implements IBusiness.uLMSFocusProductMaintIBusiness.GetFocusProduct
        Dim sb As StringBuilder = New StringBuilder()

        sb.AppendLine(" SELECT fp.factoryname,fp.factoryid,fp.workcentername,fp.workcenterid,TRIM(fp.bigproductname) bigproductname ,TRIM(fp.productname) productname,fp.focusproductid")
        sb.AppendLine(" FROM focusproduct fp")
        sb.AppendLine(" LEFT JOIN factory f ON f.factoryid = fp.factoryid")
        sb.AppendLine(" LEFT JOIN workcenter w ON w.workcenterid = fp.workcenterid")
        sb.AppendLine(" WHERE 1=1 ")

        If paraDic.ContainsKey("factoryid") Then
            If paraDic("factoryid") <> "" Then
                sb.AppendFormat(" AND fp.factoryid = '{0}'", paraDic("factoryid"))
            End If
        End If

        If paraDic.ContainsKey("workcenterid") Then
            If paraDic("workcenterid") <> "" Then
                sb.AppendFormat(" AND fp.workcenterid = '{0}'", paraDic("workcenterid"))
            End If
        End If

        If paraDic.ContainsKey("productname") Then
            If paraDic("productname") <> "" Then
                sb.AppendFormat(" AND fp.productname = '{0}' ", paraDic("productname"))
            End If
        End If

        If paraDic.ContainsKey("bigproductname") Then
            If paraDic("bigproductname") <> "" Then
                sb.AppendFormat(" AND fp.bigproductname LIKE '%{0}%'", paraDic("bigproductname"))
            End If
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(paraDic("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(paraDic("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)

    End Function

    ''' <summary>
    ''' 增加关注产品
    ''' </summary>
    ''' <param name="paraDic"></param>
    ''' <param name="resultInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AddFocusProduct(ByVal paraDic As System.Collections.Generic.Dictionary(Of String, String), ByRef resultInfo As String) As Boolean Implements IBusiness.uLMSFocusProductMaintIBusiness.AddFocusProduct
        Dim sb As New StringBuilder()
        sb.AppendLine("INSERT INTO focusproduct(")

        For Each kvp As KeyValuePair(Of String, String) In paraDic
            sb.Append(kvp.Key + ",")
        Next

        sb.Remove(sb.Length - 1, 1)
        sb.Append(") VALUES(")

        For Each kvp As KeyValuePair(Of String, String) In paraDic
            If kvp.Key = "createDate" Then
                sb.Append("to_date('" + kvp.Value + "','yyyy/mm/dd hh24:mi:ss'),")
            Else
                sb.Append("'" + kvp.Value + "',")
            End If
        Next

        sb.Remove(sb.Length - 1, 1)
        sb.Append(")")

        Try
            DatabaseHelper.ExecuteSql(sb.ToString)
            resultInfo = True
            Return resultInfo
        Catch ex As Exception
            resultInfo = False
            Return resultInfo
        End Try
    End Function

    ''' <summary>
    ''' 更新关注产品
    ''' </summary>
    ''' <param name="paraDic"></param>
    ''' <param name="resultInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateFocusProduct(ByVal paraDic As System.Collections.Generic.Dictionary(Of String, String), ByRef resultInfo As String) As Boolean Implements IBusiness.uLMSFocusProductMaintIBusiness.UpdateFocusProduct
        Dim strsql As New StringBuilder
        strsql.AppendFormat("UPDATE focusproduct fp SET fp.workcentername='{0}',fp.bigproductname = '{1}',fp.productname = '{2}',fp.workcenterid='{3}',fp.factoryname='{4}',fp.factoryid='{5}' WHERE fp.focusproductid ='{6}'",
                                    paraDic("workcentername"), paraDic("foucsProduct"), paraDic("productname"), paraDic("workcenterid"), paraDic("factoryname"), paraDic("factoryid"), paraDic("foucsProductId"))
        Try
            DatabaseHelper.ExecuteSql(strsql.ToString)
            resultInfo = True
            Return resultInfo
        Catch ex As Exception
            resultInfo = False
            Return resultInfo
        End Try
    End Function

    ''' <summary>
    ''' 删除关注产品
    ''' </summary>
    ''' <param name="paraDic"></param>
    ''' <param name="resultInfo"></param>
    ''' <returns></returns>
    ''' <remarks>修改为 in的条件语句 modify by tianFW 2018年11月12日</remarks>
    Public Function DeleteFocusProduct(ByVal paraDic As System.Collections.Generic.Dictionary(Of String, String), ByRef resultInfo As String) As Boolean Implements IBusiness.uLMSFocusProductMaintIBusiness.DeleteFocusProduct
        Dim strsql As New StringBuilder
        strsql.AppendFormat("DELETE FROM focusproduct fp WHERE fp.focusproductid IN ('{0}')", paraDic("foucsProductId"))
        Try
            DatabaseHelper.ExecuteSql(strsql.ToString)
            resultInfo = True
            Return resultInfo
        Catch ex As Exception
            resultInfo = False
            Return resultInfo
        End Try
    End Function

    ''' <summary>
    ''' 验证工区内是否已存在该关注产品
    ''' </summary>
    ''' <param name="paraDic"></param>
    ''' <param name="resultInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ValidateFocusProductIsExist(ByVal paraDic As System.Collections.Generic.Dictionary(Of String, String), ByRef resultInfo As String) As Boolean Implements IBusiness.uLMSFocusProductMaintIBusiness.ValidateFocusProductIsExist

        Dim sb As New StringBuilder()
        sb.AppendLine("SELECT COUNT(*) ct FROM focusproduct fp WHERE 1=1 ")

        If Not paraDic.ContainsKey("workcentername") Then
            Return True
            Exit Function
        End If

        If paraDic("workcentername") = "" Then

            If paraDic.ContainsKey("factoryname") Then
                If paraDic("factoryname") <> "" Then
                    sb.AppendFormat(" AND fp.factoryname='{0}'", paraDic("factoryname"))
                End If
            End If

            If paraDic.ContainsKey("productname") Then
                If paraDic("productname") <> "" Then
                    sb.AppendFormat(" AND fp.productname='{0}'", paraDic("productname"))
                End If
            End If

            sb.AppendLine(" AND fp.workcentername IS NULL ")

            Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString())
            If Convert.ToInt32(dt.Rows(0)(0).ToString()) = 0 Then
                resultInfo = False
                Return resultInfo
            Else
                resultInfo = True
                Return resultInfo
            End If
        Else
            If paraDic.ContainsKey("factoryname") Then
                If paraDic("factoryname") <> "" Then
                    sb.AppendFormat(" AND fp.factoryname='{0}'", paraDic("factoryname"))
                End If
            End If

            If paraDic.ContainsKey("productname") Then
                If paraDic("productname") <> "" Then
                    sb.AppendFormat(" AND fp.productname='{0}'", paraDic("productname"))
                End If
            End If

            sb.AppendFormat(" AND fp.workcentername ='{0}'", paraDic("workcentername"))

            Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString())
            If Convert.ToInt32(dt.Rows(0)(0).ToString()) = 0 Then
                resultInfo = False
                Return resultInfo
            Else
                resultInfo = True
                Return resultInfo
            End If

        End If
    End Function

    ''' <summary>
    ''' 添加车间通知
    ''' </summary>
    ''' <param name="paraDic"></param>
    ''' <param name="resultInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveFactoryNotice(ByVal paraDic As System.Collections.Generic.Dictionary(Of String, String), ByRef resultInfo As String) As Boolean Implements IBusiness.uLMSFocusProductMaintIBusiness.SaveFactoryNotice
        Dim sb As New StringBuilder()
        sb.AppendFormat(" UPDATE factory f SET f.notes='{0}' WHERE f.factoryid='{1}'", paraDic("strNotice"), paraDic("factoryid"))
        Try
            DatabaseHelper.ExecuteSql(sb.ToString())
            resultInfo = True
            Return resultInfo
        Catch ex As Exception
            resultInfo = False
            Return resultInfo
        End Try
        Return True
    End Function

    ''' <summary>
    ''' 查车间工区
    ''' </summary>
    ''' <param name="paraDic"></param>
    ''' <param name="resultInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkCenterInFactory(ByVal paraDic As System.Collections.Generic.Dictionary(Of String, String)) As System.Data.DataTable Implements IBusiness.uLMSFocusProductMaintIBusiness.GetWorkCenterInFactory
        Dim sb As StringBuilder = New StringBuilder
        sb.AppendLine(" SELECT w.workcentername,w.workcenterid,w.description")
        sb.AppendLine(" FROM workcenter w")
        sb.AppendLine(" LEFT JOIN Factory f ON f.factoryid = w.factoryid")
        If paraDic.ContainsKey("FactoryId") Then
            If paraDic("FactoryId") <> "" Then
                sb.AppendFormat(" WHERE f.factoryid = '{0}'", paraDic("FactoryId"))
            End If
        End If

        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString())
        Return dt
    End Function

    ''' <summary>
    ''' 查车间
    ''' </summary>
    ''' <param name="paraDic"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetFactory(ByVal paraDic As System.Collections.Generic.Dictionary(Of String, String)) As System.Data.DataTable Implements IBusiness.uLMSFocusProductMaintIBusiness.GetFactory
        Dim sb As StringBuilder = New StringBuilder
        sb.AppendLine(" SELECT f.factoryname,f.factoryid,f.description")
        sb.AppendLine(" FROM Factory f")
        If paraDic.ContainsKey("FactoryId") Then
            If paraDic("FactoryId") <> "" Then
                sb.AppendFormat(" WHERE f.factoryid = '{0}'", paraDic("FactoryId"))
            End If
        End If

        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString())
        Return dt
    End Function


    ''' <summary>
    ''' 查本车间产品
    ''' </summary>
    ''' <param name="paraDic"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetProductInFactory(ByVal paraDic As System.Collections.Generic.Dictionary(Of String, String)) As System.Data.DataTable Implements IBusiness.uLMSFocusProductMaintIBusiness.GetProductInFactory
        Dim sb As New StringBuilder()
        sb.AppendLine("SELECT pb.productname FROM product p")
        sb.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid")
        sb.AppendLine("LEFT JOIN factory f ON p.factoryid = f.factoryid")
        sb.AppendLine("WHERE pb.productname IS NOT NULL")

        If paraDic.ContainsKey("factoryid") Then

            If paraDic("factoryid") <> "" Then
                sb.AppendFormat(" AND f.factoryid = '{0}'", paraDic("factoryid"))
            End If

        End If

        If paraDic.ContainsKey("productname") Then
            If paraDic("productname") <> "" Then
                sb.AppendFormat(" AND pb.productname LIKE '%{0}%' ", paraDic("productname"))
            End If
        End If
        sb.AppendLine("ORDER BY pb.productname")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString())
        Return dt
    End Function
End Class
